package org.termine.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import org.primefaces.model.map.DefaultMapModel;
import org.primefaces.model.map.LatLng;
import org.primefaces.model.map.MapModel;
import org.primefaces.model.map.Marker;
import org.termine.entity.Appointment;
import org.termine.entity.ClientProviderScheduleEvent;
import org.termine.entity.ProviderAppointment;
import org.termine.entity.ServiceSelectItem;

@ManagedBean
@ApplicationScoped
public class AppointmentDAO extends BaseDAO {

	public void addAppointment(Connection connection, Appointment appointment) throws Exception {
		PreparedStatement statement = null;
		String statementString = "insert into APPOINTMENT (L_ID, S_ID, C_ID, AP_START) values (?,?,?,?)";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, appointment.getLocation_id());
			statement.setInt(2, appointment.getService_id());
			statement.setInt(3, appointment.getClient_id());
			statement.setTimestamp(4, new Timestamp(appointment.getStart().getTime()));
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public void addAppointment(Connection connection, ServiceSelectItem service,
			Appointment appointment) throws Exception {
		PreparedStatement statement = null;
		String statementString = "insert into APPOINTMENT (L_ID, S_ID, C_ID, AP_START, AP_CALENDAR) values (?,?,?,?,?)";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, appointment.getLocation_id());
			statement.setInt(2, appointment.getService_id());
			statement.setInt(3, appointment.getClient_id());
			statement.setTimestamp(4, new Timestamp(appointment.getStart().getTime()));
			Format formatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
			statement.setString(5, "<vevent><properties><dtstamp><date-time>" + formatter.format(new Date())
					+ "</date-time></dtstamp><dtstart><date-time>" + formatter.format(appointment.getStart())
					+ "</date-time></dtstart><duration>PT" + service.getDurationInMin() + "M</duration><summary><text>" + service.getName() 
					+ "</text></summary><uid><text>" + UUID.randomUUID().toString()
					+ "</text></uid></properties></vevent>");
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public void deleteAppointment(Connection connection, Integer appointmentId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "delete from APPOINTMENT where AP_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, appointmentId);
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public boolean overlapsWithExistingAppointment(Connection connection, Date from, Date to,
			Integer duration, Integer providerId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select count(*) as X from (select * from APPOINTMENT ap join SERVICE s on s.S_ID = ap.S_ID where s.P_ID = ?) where AP_START <= ? and ? <= AP_START + ? minutes";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			statement.setTimestamp(2, new Timestamp(to.getTime()));
			statement.setTimestamp(3, new Timestamp(from.getTime()));
			statement.setInt(4, duration);
			ResultSet resultSet = statement.executeQuery();
			resultSet.next();
			return resultSet.getInt("X") != 0;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public List<ClientProviderScheduleEvent> getAllAppointmentsForClient(Connection connection,
			Integer clientId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select S_DURATION_IN_MIN, l.L_ID, L_COUNTRY, L_STREET, L_ZIP, L_CITY, "
				+ "PD_FORENAME, PD_EMAIL, PD_PHONE_NUMBER, PD_SURNAME, S_CANCEL_DEADLINE_IN_MIN, AP_ID, "
				+ "AP_START, S_NAME, S_DESCRIPTION, db2gse.ST_X (L_POSITION) X, db2gse.ST_Y (L_POSITION) Y "
				+ "from APPOINTMENT a join LOCATION l on l.L_ID = a.L_ID "
				+ "join SERVICE s on s.S_ID = a.S_ID join PROVIDER p on p.P_ID = s.P_ID "
				+ "join PERSONAL_DATA pd on pd.PD_ID = p.PD_ID where a.C_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, clientId);
			ResultSet resultSet = statement.executeQuery();
			List<ClientProviderScheduleEvent> result = new LinkedList<ClientProviderScheduleEvent>();
			while (resultSet.next()) {
				ClientProviderScheduleEvent appointment = new ClientProviderScheduleEvent();
				Integer integer;
				String string;
				Date date;
				double decimal;

				integer = new Integer(resultSet.getInt("S_DURATION_IN_MIN"));
				if (!resultSet.wasNull()) {
					appointment.setDurationInMin(integer);
				}
				integer = new Integer(resultSet.getInt("L_ID"));
				if (!resultSet.wasNull()) {
					appointment.setLocationId(integer);
				}
				integer = new Integer(resultSet.getInt("AP_ID"));
				if (!resultSet.wasNull()) {
					appointment.setAppointmentId(integer);
				}
				date = new Date(resultSet.getTimestamp("AP_START").getTime());
				if (!resultSet.wasNull()) {
					appointment.setAppointmentStart(date);
				}
				string = resultSet.getString("S_NAME");
				if (!resultSet.wasNull()) {
					appointment.setServiceName(string);
				}
				string = resultSet.getString("S_DESCRIPTION");
				if (!resultSet.wasNull()) {
					appointment.setServiceDescription(string);
				}
				integer = new Integer(resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					appointment.setServiceCancelDeadlineInMin(integer);
				}
				string = resultSet.getString("PD_FORENAME");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderForename(string);
				}
				string = resultSet.getString("PD_SURNAME");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderSurname(string);
				}
				string = resultSet.getString("PD_PHONE_NUMBER");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderPhoneNumber(string);
				}
				string = resultSet.getString("PD_EMAIL");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderEmail(string);
				}
				string = resultSet.getString("L_COUNTRY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCountry(string);
				}
				string = resultSet.getString("L_CITY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCity(string);
				}
				string = resultSet.getString("L_ZIP");
				if (!resultSet.wasNull()) {
					appointment.setLocationZip(string);
				}
				string = resultSet.getString("L_STREET");
				if (!resultSet.wasNull()) {
					appointment.setLocationStreet(string);
				}
				decimal = resultSet.getDouble("X");
				if (!resultSet.wasNull()) {
					appointment.setLocationLatitude(decimal);
				}
				decimal = resultSet.getDouble("Y");
				if (!resultSet.wasNull()) {
					appointment.setLocationLongitude(decimal);
				}
				MapModel mapModel = new DefaultMapModel();
				appointment.setMapModel(mapModel);
				appointment.getMapModel().addOverlay(
						new Marker(new LatLng(appointment.getLocationLatitude(), appointment
								.getLocationLongitude()), "appointment location"));
				result.add(appointment);
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public List<ClientProviderScheduleEvent> getAllAppointmentsForProvider(Connection connection,
			Integer providerId, Date from, Date to) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select S_DURATION_IN_MIN, AP_CALENDAR, AP_ID, S_NAME, PD_EMAIL, PD_PHONE_NUMBER, PD_SURNAME, "
				+ "PD_FORENAME, S_CANCEL_DEADLINE_IN_MIN, S_DESCRIPTION, AP_START, a.L_ID, L_COUNTRY, L_CITY, L_ZIP, "
				+ "L_STREET, db2gse.ST_X (L_POSITION) X, db2gse.ST_Y (L_POSITION) Y "
				+ "from SERVICE s join APPOINTMENT a on s.S_ID = a.S_ID "
				+ "join CLIENT c on c.C_ID = a.C_ID join PERSONAL_DATA pd on c.C_ID = pd.C_ID "
				+ "join LOCATION l on a.L_ID = l.L_ID where s.P_ID = ? and "
				+ "a.AP_START >= ? and ? >= a.AP_START + s.S_DURATION_IN_MIN minutes";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			statement.setTimestamp(2, new Timestamp(from.getTime()));
			statement.setTimestamp(3, new Timestamp(to.getTime()));
			ResultSet resultSet = statement.executeQuery();
			List<ClientProviderScheduleEvent> result = new LinkedList<ClientProviderScheduleEvent>();
			while (resultSet.next()) {
				ClientProviderScheduleEvent appointment = new ClientProviderScheduleEvent();
				Integer integer;
				String string;
				Date date;
				double decimal;

				integer = new Integer(resultSet.getInt("S_DURATION_IN_MIN"));
				if (!resultSet.wasNull()) {
					appointment.setDurationInMin(integer);
				}
				integer = new Integer(resultSet.getInt("L_ID"));
				if (!resultSet.wasNull()) {
					appointment.setLocationId(integer);
				}
				integer = new Integer(resultSet.getInt("AP_ID"));
				if (!resultSet.wasNull()) {
					appointment.setAppointmentId(integer);
				}
				date = new Date(resultSet.getTimestamp("AP_START").getTime());
				if (!resultSet.wasNull()) {
					appointment.setAppointmentStart(date);
				}
				string = resultSet.getString("S_NAME");
				if (!resultSet.wasNull()) {
					appointment.setServiceName(string);
				}
				string = resultSet.getString("S_DESCRIPTION");
				if (!resultSet.wasNull()) {
					appointment.setServiceDescription(string);
				}
				integer = new Integer(resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					appointment.setServiceCancelDeadlineInMin(integer);
				}
				string = resultSet.getString("PD_FORENAME");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderForename(string);
				}
				string = resultSet.getString("PD_SURNAME");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderSurname(string);
				}
				string = resultSet.getString("PD_PHONE_NUMBER");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderPhoneNumber(string);
				}
				string = resultSet.getString("PD_EMAIL");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderEmail(string);
				}
				string = resultSet.getString("L_COUNTRY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCountry(string);
				}
				string = resultSet.getString("L_CITY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCity(string);
				}
				string = resultSet.getString("L_ZIP");
				if (!resultSet.wasNull()) {
					appointment.setLocationZip(string);
				}
				string = resultSet.getString("L_STREET");
				if (!resultSet.wasNull()) {
					appointment.setLocationStreet(string);
				}
				decimal = resultSet.getDouble("X");
				if (!resultSet.wasNull()) {
					appointment.setLocationLatitude(decimal);
				}
				decimal = resultSet.getDouble("Y");
				if (!resultSet.wasNull()) {
					appointment.setLocationLongitude(decimal);
				}
				string = resultSet.getString("AP_CALENDAR");
				if (!resultSet.wasNull()) {
					appointment.setXmlCalendar(string);
				}
				MapModel mapModel = new DefaultMapModel();
				appointment.setMapModel(mapModel);
				appointment.getMapModel().addOverlay(
						new Marker(new LatLng(appointment.getLocationLatitude(), appointment
								.getLocationLongitude()), "appointment location"));
				result.add(appointment);
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public ClientProviderScheduleEvent getBeforeAppointmentForProvider(Connection connection,
			Integer providerId, Date nextStart) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select S_DURATION_IN_MIN, AP_ID, S_NAME, PD_EMAIL, PD_PHONE_NUMBER, PD_SURNAME, "
				+ "PD_FORENAME, S_CANCEL_DEADLINE_IN_MIN, S_DESCRIPTION, AP_START, a.L_ID, L_COUNTRY, L_CITY, L_ZIP, "
				+ "L_STREET, db2gse.ST_X (L_POSITION) X, db2gse.ST_Y (L_POSITION) Y "
				+ "from SERVICE s join APPOINTMENT a on s.S_ID = a.S_ID "
				+ "join CLIENT c on c.C_ID = a.C_ID join PERSONAL_DATA pd on c.C_ID = pd.C_ID "
				+ "join LOCATION l on a.L_ID = l.L_ID where s.P_ID = ? and "
				+ "a.AP_START + s.S_DURATION_IN_MIN minutes < ? order by a.AP_START desc";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			statement.setTimestamp(2, new Timestamp(nextStart.getTime()));
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				ClientProviderScheduleEvent appointment = new ClientProviderScheduleEvent();
				Integer integer;
				String string;
				Date date;
				double decimal;

				integer = new Integer(resultSet.getInt("S_DURATION_IN_MIN"));
				if (!resultSet.wasNull()) {
					appointment.setDurationInMin(integer);
				}
				integer = new Integer(resultSet.getInt("L_ID"));
				if (!resultSet.wasNull()) {
					appointment.setLocationId(integer);
				}
				integer = new Integer(resultSet.getInt("AP_ID"));
				if (!resultSet.wasNull()) {
					appointment.setAppointmentId(integer);
				}
				date = new Date(resultSet.getTimestamp("AP_START").getTime());
				if (!resultSet.wasNull()) {
					appointment.setAppointmentStart(date);
				}
				string = resultSet.getString("S_NAME");
				if (!resultSet.wasNull()) {
					appointment.setServiceName(string);
				}
				string = resultSet.getString("S_DESCRIPTION");
				if (!resultSet.wasNull()) {
					appointment.setServiceDescription(string);
				}
				integer = new Integer(resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					appointment.setServiceCancelDeadlineInMin(integer);
				}
				string = resultSet.getString("PD_FORENAME");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderForename(string);
				}
				string = resultSet.getString("PD_SURNAME");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderSurname(string);
				}
				string = resultSet.getString("PD_PHONE_NUMBER");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderPhoneNumber(string);
				}
				string = resultSet.getString("PD_EMAIL");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderEmail(string);
				}
				string = resultSet.getString("L_COUNTRY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCountry(string);
				}
				string = resultSet.getString("L_CITY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCity(string);
				}
				string = resultSet.getString("L_ZIP");
				if (!resultSet.wasNull()) {
					appointment.setLocationZip(string);
				}
				string = resultSet.getString("L_STREET");
				if (!resultSet.wasNull()) {
					appointment.setLocationStreet(string);
				}
				decimal = resultSet.getDouble("X");
				if (!resultSet.wasNull()) {
					appointment.setLocationLatitude(decimal);
				}
				decimal = resultSet.getDouble("Y");
				if (!resultSet.wasNull()) {
					appointment.setLocationLongitude(decimal);
				}
				return appointment;
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public ClientProviderScheduleEvent getAfterAppointmentForProvider(Connection connection,
			Integer providerId, Date nextEnd) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select S_DURATION_IN_MIN, AP_ID, S_NAME, PD_EMAIL, PD_PHONE_NUMBER, PD_SURNAME, "
				+ "PD_FORENAME, S_CANCEL_DEADLINE_IN_MIN, S_DESCRIPTION, AP_START, a.L_ID, L_COUNTRY, L_CITY, L_ZIP, "
				+ "L_STREET, db2gse.ST_X (L_POSITION) X, db2gse.ST_Y (L_POSITION) Y "
				+ "from SERVICE s join APPOINTMENT a on s.S_ID = a.S_ID "
				+ "join CLIENT c on c.C_ID = a.C_ID join PERSONAL_DATA pd on c.C_ID = pd.C_ID "
				+ "join LOCATION l on a.L_ID = l.L_ID where s.P_ID = ? and "
				+ "a.AP_START > ? order by a.AP_START desc";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			statement.setTimestamp(2, new Timestamp(nextEnd.getTime()));
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				ClientProviderScheduleEvent appointment = new ClientProviderScheduleEvent();
				Integer integer;
				String string;
				Date date;
				double decimal;

				integer = new Integer(resultSet.getInt("S_DURATION_IN_MIN"));
				if (!resultSet.wasNull()) {
					appointment.setDurationInMin(integer);
				}
				integer = new Integer(resultSet.getInt("L_ID"));
				if (!resultSet.wasNull()) {
					appointment.setLocationId(integer);
				}
				integer = new Integer(resultSet.getInt("AP_ID"));
				if (!resultSet.wasNull()) {
					appointment.setAppointmentId(integer);
				}
				date = new Date(resultSet.getTimestamp("AP_START").getTime());
				if (!resultSet.wasNull()) {
					appointment.setAppointmentStart(date);
				}
				string = resultSet.getString("S_NAME");
				if (!resultSet.wasNull()) {
					appointment.setServiceName(string);
				}
				string = resultSet.getString("S_DESCRIPTION");
				if (!resultSet.wasNull()) {
					appointment.setServiceDescription(string);
				}
				integer = new Integer(resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					appointment.setServiceCancelDeadlineInMin(integer);
				}
				string = resultSet.getString("PD_FORENAME");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderForename(string);
				}
				string = resultSet.getString("PD_SURNAME");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderSurname(string);
				}
				string = resultSet.getString("PD_PHONE_NUMBER");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderPhoneNumber(string);
				}
				string = resultSet.getString("PD_EMAIL");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderEmail(string);
				}
				string = resultSet.getString("L_COUNTRY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCountry(string);
				}
				string = resultSet.getString("L_CITY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCity(string);
				}
				string = resultSet.getString("L_ZIP");
				if (!resultSet.wasNull()) {
					appointment.setLocationZip(string);
				}
				string = resultSet.getString("L_STREET");
				if (!resultSet.wasNull()) {
					appointment.setLocationStreet(string);
				}
				decimal = resultSet.getDouble("X");
				if (!resultSet.wasNull()) {
					appointment.setLocationLatitude(decimal);
				}
				decimal = resultSet.getDouble("Y");
				if (!resultSet.wasNull()) {
					appointment.setLocationLongitude(decimal);
				}
				return appointment;
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public List<ClientProviderScheduleEvent> getAllAppointmentsForProvider(Connection connection,
			Integer providerId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select S_DURATION_IN_MIN, l.L_ID, L_COUNTRY, L_STREET, L_ZIP, L_CITY, "
				+ "PD_FORENAME, PD_EMAIL, PD_PHONE_NUMBER, PD_SURNAME, S_CANCEL_DEADLINE_IN_MIN, AP_ID, "
				+ "AP_START, S_NAME, S_DESCRIPTION, db2gse.ST_X (L_POSITION) X, db2gse.ST_Y (L_POSITION) Y "
				+ "from APPOINTMENT a join LOCATION l on l.L_ID = a.L_ID "
				+ "join SERVICE s on s.S_ID = a.S_ID join PROVIDER p on p.P_ID = s.P_ID " 
				+ "join CLIENT c on a.C_ID = c.C_ID "
				+ "join PERSONAL_DATA pd on pd.PD_ID = c.PD_ID where s.P_ID = ?";

		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			ResultSet resultSet = statement.executeQuery();
			List<ClientProviderScheduleEvent> result = new LinkedList<ClientProviderScheduleEvent>();
			while (resultSet.next()) {
				ClientProviderScheduleEvent appointment = new ClientProviderScheduleEvent();
				Integer integer;
				String string;
				Date date;
				double decimal;

				integer = new Integer(resultSet.getInt("S_DURATION_IN_MIN"));
				if (!resultSet.wasNull()) {
					appointment.setDurationInMin(integer);
				}
				integer = new Integer(resultSet.getInt("L_ID"));
				if (!resultSet.wasNull()) {
					appointment.setLocationId(integer);
				}
				integer = new Integer(resultSet.getInt("AP_ID"));
				if (!resultSet.wasNull()) {
					appointment.setAppointmentId(integer);
				}
				date = new Date(resultSet.getTimestamp("AP_START").getTime());
				if (!resultSet.wasNull()) {
					appointment.setAppointmentStart(date);
				}
				string = resultSet.getString("S_NAME");
				if (!resultSet.wasNull()) {
					appointment.setServiceName(string);
				}
				string = resultSet.getString("S_DESCRIPTION");
				if (!resultSet.wasNull()) {
					appointment.setServiceDescription(string);
				}
				integer = new Integer(resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					appointment.setServiceCancelDeadlineInMin(integer);
				}
				string = resultSet.getString("PD_FORENAME");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderForename(string);
				}
				string = resultSet.getString("PD_SURNAME");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderSurname(string);
				}
				string = resultSet.getString("PD_PHONE_NUMBER");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderPhoneNumber(string);
				}
				string = resultSet.getString("PD_EMAIL");
				if (!resultSet.wasNull()) {
					appointment.setClientProviderEmail(string);
				}
				string = resultSet.getString("L_COUNTRY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCountry(string);
				}
				string = resultSet.getString("L_CITY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCity(string);
				}
				string = resultSet.getString("L_ZIP");
				if (!resultSet.wasNull()) {
					appointment.setLocationZip(string);
				}
				string = resultSet.getString("L_STREET");
				if (!resultSet.wasNull()) {
					appointment.setLocationStreet(string);
				}
				decimal = resultSet.getDouble("X");
				if (!resultSet.wasNull()) {
					appointment.setLocationLatitude(decimal);
				}
				decimal = resultSet.getDouble("Y");
				if (!resultSet.wasNull()) {
					appointment.setLocationLongitude(decimal);
				}
				MapModel mapModel = new DefaultMapModel();
				appointment.setMapModel(mapModel);
				appointment.getMapModel().addOverlay(
						new Marker(new LatLng(appointment.getLocationLatitude(), appointment
								.getLocationLongitude()), "appointment location"));
				result.add(appointment);
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public List<ProviderAppointment> getAllProviderAppointmentsForProvider(Connection connection,
			Integer providerId, Date from, Date to) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select AP_START, S_DURATION_IN_MIN, S_NAME, S_DESCRIPTION, L_COUNTRY, L_CITY, L_ZIP, L_STREET, db2gse.ST_X (L_POSITION) X, db2gse.ST_Y (L_POSITION) Y "
				+ "from SERVICE s join APPOINTMENT a on s.S_ID = a.S_ID "
				+ "join CLIENT c on c.C_ID = a.C_ID join PERSONAL_DATA pd on c.C_ID = pd.C_ID "
				+ "join LOCATION l on a.L_ID = l.L_ID where s.P_ID = ? and AP_START >= ? and AP_START + S_DURATION_IN_MIN minutes <= ? order by AP_START asc";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			statement.setTimestamp(2, new Timestamp(from.getTime()));
			statement.setTimestamp(3, new Timestamp(to.getTime()));
			ResultSet resultSet = statement.executeQuery();
			List<ProviderAppointment> result = new LinkedList<ProviderAppointment>();
			while (resultSet.next()) {
				ProviderAppointment appointment = new ProviderAppointment();
				Integer integer;
				String string;
				Date date;
				double decimal;

				date = new Date(resultSet.getTimestamp("AP_START").getTime());
				if (!resultSet.wasNull()) {
					appointment.setAppointmentStart(date);
				}
				if (appointment.getAppointmentStart() != null) {
					integer = new Integer(resultSet.getInt("S_DURATION_IN_MIN"));
					if (!resultSet.wasNull()) {
						Calendar calendar = new GregorianCalendar();
						calendar.setTime(appointment.getAppointmentStart());
						calendar.add(Calendar.MINUTE, integer);
						appointment.setAppointmentEnd(calendar.getTime());
					}
				}
				string = resultSet.getString("S_NAME");
				if (!resultSet.wasNull()) {
					appointment.setServiceName(string);
				}
				string = resultSet.getString("S_DESCRIPTION");
				if (!resultSet.wasNull()) {
					appointment.setServiceDescription(string);
				}
				string = resultSet.getString("L_COUNTRY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCountry(string);
				}
				string = resultSet.getString("L_CITY");
				if (!resultSet.wasNull()) {
					appointment.setLocationCity(string);
				}
				string = resultSet.getString("L_ZIP");
				if (!resultSet.wasNull()) {
					appointment.setLocationZip(string);
				}
				string = resultSet.getString("L_STREET");
				if (!resultSet.wasNull()) {
					appointment.setLocationStreet(string);
				}
				decimal = resultSet.getDouble("X");
				if (!resultSet.wasNull()) {
					appointment.setLocationLatitude(decimal);
				}
				decimal = resultSet.getDouble("Y");
				if (!resultSet.wasNull()) {
					appointment.setLocationLongitude(decimal);
				}
				result.add(appointment);
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

}
